﻿' (c) Copyright Microsoft Corporation.
' This source is subject to the Microsoft Public License.
' See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
' All other rights reserved.
Imports System
Imports System.Collections
Imports System.Linq
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Xml.Linq
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb

' To allow this Web Service to be called from script, using ASP.NET AJAX, 
' uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
Public Class AutoComplete
    Inherits System.Web.Services.WebService
    Dim cn As New SqlClient.SqlConnection()
    Dim ds As New DataSet
    Dim dt As New DataTable
    <WebMethod()> _
    Public Function GetCompletionList(ByVal prefixText As String, _
 ByVal count As Integer) As String()

        Dim cn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("BookMeetConnString").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand
        cmd.CommandType = CommandType.Text
        'Compare String From Textbox(prefixText) 
        'AND String From Column in DataBase(CompanyName)
        'If String from DataBase is equal to String from TextBox(prefixText) 
        'then add it to return ItemList
        '-----I defined a parameter instead of passing value 
        'directly to prevent SQL injection--------'
        cmd.CommandText = "select * from booklist Where Title like @myParameter"
        cmd.Parameters.AddWithValue("@myParameter", "%" + prefixText + "%")

        Try
            cn.Open()
            cmd.ExecuteNonQuery()
            Dim da As New OleDbDataAdapter(cmd)
            da.Fill(ds)
        Catch ex As Exception
        Finally
            cn.Close()
        End Try



        'Then return List of string(txtItems) as result

        Dim txtItems As New List(Of String)
        Dim dbValues As String

        For Each row As DataRow In dt.Rows

            ''String From DataBase(dbValues)
            dbValues = row("Title").ToString()
            dbValues = dbValues.ToLower()
            txtItems.Add(dbValues)

        Next

        Return txtItems.ToArray()

    End Function

End Class